package gen.tools.deployByDelivery;

import gen.tools.Widgets;
import gen.tools.structures.Req;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class Query4SDEP extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public Query4SDEP() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		final Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/json");
		
		Connection conn = null;
		Connection tarConn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		
		final ArrayList<Qdep> qdeps = new ArrayList<Qdep>();
		
		try {
			conn = wid.getConn();
			//建立<分公司>資料庫連線
			tarConn = wid.getTarConnection(request, 7);
			
			
			String sql = "SELECT t1.deliverySN,t1.deliveryTarSN,t1.deliveryComment,t1.deplmainSN,t2.tarDeplmainSN,t2.createdDateTime FROM deltask t1,deplmain t2 WHERE t1.deplmainSN=t2.deplmainSN AND statusSN=3 ORDER BY t1.deliverySN DESC";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while( rs.next() ) {
				final Qdep qdep = new Qdep();
				final ArrayList<FileStr> fsts = new ArrayList<FileStr>();
				final ArrayList<Req> reqs = new ArrayList<Req>();
				
				final int deliverySN = rs.getInt("deliverySN");
				final int deplMainSN = rs.getInt("deplmainSN");
				qdep.setDeliveryComment( rs.getString("deliveryComment") );
				qdep.setDeliverySN( deliverySN );
				qdep.setDeliveryTarSN( rs.getInt("deliveryTarSN") );
				qdep.setDeplmainSN( deplMainSN );
				qdep.setTarDeplmainSN( rs.getInt("tarDeplmainSN") );
				qdep.setCdt( wid.changeTimeFormatUI( rs.getTimestamp("createdDateTime") ) );
				
				sql = "SELECT t1.depmainSN,t1.createdDateTime,t2.empName FROM depmain t1,emps t2 WHERE t1.creator=t2.empId AND t1.deplmainSN=? ORDER BY t1.createdDateTime DESC";
				ps2 = conn.prepareStatement(sql);
				ps2.setInt(1, deplMainSN);
				rs2 = ps2.executeQuery();
				if ( rs2.next() ) {
					qdep.setLastDeployerName( rs2.getString("empName") );
					qdep.setLastDeployId( String.valueOf( rs2.getInt("depmainSN") ) ); 
					qdep.setLastDeployDT( wid.changeTimeFormatUI( rs2.getTimestamp("createdDateTime") ) );	
				} else{
					qdep.setLastDeployerName( "" );
					qdep.setLastDeployDT( "" );	
					qdep.setLastDeployId("");
				}
				
				
				sql = "SELECT fromFile,fileName,fromVer,toVer FROM delsvn t1,deltask t2,depldetail t3,tmpmpdeplf t4,fdepldetail t5 WHERE t3.localFile=t1.fromFile AND t1.deliverySN=t2.deliverySN AND t1.deliverySN=? AND t5.fDeplmainSN=t4.fDeplmainSN AND t5.fDeplDetailId=t4.fDeplDetailId AND t3.deplmainSN=t4.deplmain AND t3.depldetailid=t4.depldetailid AND t3.deplmainSN=t2.deplmainSN";
				ps2 = tarConn.prepareStatement(sql);
				ps2.setInt(1, rs.getInt("deliveryTarSN"));
				rs2 = ps2.executeQuery();
				while( rs2.next() ) {
					final FileStr fst = new FileStr();
					fst.setOriFn( rs2.getString("fileName") );
					fst.setFileName( rs2.getString("fromFile") );
					fst.setLocalVer( rs2.getInt("fromVer") );
					fst.setTargetVer( rs2.getInt("toVer") );
					fsts.add(fst);
				}
				
				sql = "SELECT DISTINCT reqId,reqSID,reqDesc FROM mpreqfiles t1,reqs t2 WHERE t1.reqSN=t2.reqId AND deplmainSN=?";
				ps2 = conn.prepareStatement(sql);
				ps2.setInt(1, deplMainSN);
				rs2 = ps2.executeQuery();
				while( rs2.next() ) {
					final Req req = new Req();
					req.setReqId( rs2.getInt("reqId") );
					req.setReqSID( rs2.getString("reqSID") );
					req.setReqDesc( rs2.getString("reqDesc") );
					reqs.add(req);
				}
				qdep.setFiles(fsts);
				qdep.setReqs(reqs);
				qdeps.add(qdep);
			}
		} catch(Exception e) {
			wid.errLog(e);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( rs2 !=null ) { rs2.close(); }
				if ( ps2 !=null ) { ps2.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }
				if ( tarConn !=null ) { tarConn.close(); }
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		response.getWriter().write( new Gson().toJson(qdeps) );
	}

}
